﻿/**************************************************************
* Copyright (C) 2022 www.eipflow.com 孙泽伟版权所有(盗版必究)
*
* 作者: 孙泽伟(QQ 1039318332)
* 创建时间: 2022/01/12 22:40:15
* 文件名: 
* 描述: 
* 
* 修改历史
* 修改人：
* 时间：
* 修改说明：
*
**************************************************************/
using Dapper;
using EIP.Agile.Models.Dtos.DataBase;
using EIP.Agile.Models.Enums;
using EIP.Agile.Repository;
using EIP.Base.Models.Entities.Agile;
using EIP.Base.Repository.Fixture;
using EIP.Common.Extension;
using EIP.Common.Language.Resource;
using EIP.Common.Models;
using EIP.Common.Models.Dtos.DataBase;
using EIP.Common.Models.Paging;
using EIP.Common.Models.Resx;
using EIP.Common.Models.Tree;
using EIP.Common.Repository;
using EIP.Common.Util;
using MySql.Data.MySqlClient;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace EIP.Agile.Logic.Impl
{
    /// <summary>
    /// 系统配置文件接口实现
    /// </summary>
    public class AgileDataBaseLogic : IAgileDataBaseLogic
    {
        #region 构造函数
        /// <summary>
        /// 关联文本
        /// </summary>
        private readonly string relationTxt = "_Txt";
        /// <summary>
        /// 需要建立值和键两个字段
        /// </summary>
        private readonly string[] _relationField = { "radio", "checkbox", "organization", "dictionary", "user", "select", "district" };
        /// <summary>
        /// 多选,建立附表字段
        /// </summary>
        private readonly string[] _multipleField = { "organization", "dictionary", "user", "select", "checkbox", "district" };

        /// <summary>
        /// filterSql字段，是否过滤数据字段
        /// </summary>
        private readonly string[] _filterSqlField = { "switch", "sign" };

        /// <summary>
        /// 无需创建字段
        /// </summary>
        private readonly string[] _noCreateField = { "batch", "uploadimg", "uploadfile", "wps" };
        private readonly string _connectionString = ConfigurationUtil.GetSection("EIP:ConnectionString");
        private readonly string _connectionType = ConfigurationUtil.GetSection("EIP:ConnectionType").ToLower();
        private readonly IAgileDataBaseRepository _dataBaseRepository;
        private readonly IAgileConfigLogic _agileConfigLogic;
        /// <summary>
        /// 
        /// </summary>
        public AgileDataBaseLogic(IAgileDataBaseRepository dataBaseRepository,
            IAgileConfigLogic agileConfigLogic)
        {
            _dataBaseRepository = dataBaseRepository;
            _agileConfigLogic = agileConfigLogic;
        }

        #endregion

        #region 方法
        public async Task<OperateStatus<IEnumerable<BaseTree>>> FindTableTree()
        {
            List<BaseTree> jsTree = new List<BaseTree>();

            //循环表
            var tables = (await FindDataBaseTable()).Data.ToList();
            foreach (var table in tables)
            {
                jsTree.Add(new BaseTree
                {
                    parent = Guid.Empty,
                    text = table.Name + (table.Description.IsNotNullOrEmpty() ? "-[" + table.Description + "]" : ""),
                    id = table.Name,
                    icon = "table"
                });
            }

            //获取字段
            var columns = (await FindDataBaseColumns(new AgileDataBaseTableDto()
            {
                Name = tables.Select(s => s.Name).ExpandAndToString()
            })).Data.ToList();
            foreach (var table in tables)
            {
                foreach (var column in columns.Where(w => w.TableName == table.Name))
                {
                    jsTree.Add(new BaseTree
                    {
                        parent = table.Name,
                        text = column.Name + "(" + column.DataType + (column.DataType.Contains("char") ? "(" + column.MaxLength + ")" : "") + "," + (column.IsNullable.ToLower() == "yes" ? "null" : "not null") + ")"/* +(column.ColumnDescription.IsNullOrEmpty()?"": "-[" + column.ColumnDescription + "]")*/ ,
                        id = table.Name + "|" + column.Name,
                        icon = "bars"
                    });
                }
            }
            return OperateStatus<IEnumerable<BaseTree>>.Success(jsTree);
        }

        /// <summary>
        /// 查看对应数据库空间占用情况
        /// </summary>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseSpaceOutput>>> FindDataBaseSpaceused()
        {
            return OperateStatus<IEnumerable<AgileDataBaseSpaceOutput>>.Success(await _dataBaseRepository.FindDataBaseSpaceused());
        }

        /// <summary>
        /// 获取对应数据库表信息
        /// </summary>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseTableDto>>> FindDataBaseTable()
        {
            return OperateStatus<IEnumerable<AgileDataBaseTableDto>>.Success(await _dataBaseRepository.FindDataBaseTable());
        }
        /// <summary>
        /// 获取对应数据库表信息
        /// </summary>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseTableDto>>> FindDataBaseView()
        {
            return OperateStatus<IEnumerable<AgileDataBaseTableDto>>.Success(await _dataBaseRepository.FindDataBaseView());
        }

        /// <summary>
        /// 获取对应数据库表信息
        /// </summary>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseTableDto>>> FindDataBaseProc()
        {
            return OperateStatus<IEnumerable<AgileDataBaseTableDto>>.Success(await _dataBaseRepository.FindDataBaseProc());
        }
        /// <summary>
        /// 获取对应表列信息
        /// </summary>
        /// <param name="doubleWayDto"></param>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseColumnDto>>> FindDataBaseColumns(AgileDataBaseTableDto doubleWayDto)
        {
            return OperateStatus<IEnumerable<AgileDataBaseColumnDto>>.Success(await _dataBaseRepository.FindDataBaseColumns(doubleWayDto));
        }
        /// <summary>
        /// 获取对应数据库表信息
        /// </summary>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseTableDto>>> FindDataBaseWorkflowTables()
        {
            return OperateStatus<IEnumerable<AgileDataBaseTableDto>>.Success((await _dataBaseRepository.FindDataBaseTable()).ToList().Where(w => w.Name.Contains("Form_")));
        }

        /// <summary>
        /// 获取对应表列信息
        /// </summary>
        /// <param name="doubleWayDto"></param>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseColumnDto>>> FindWorkflowDataBaseColumnsList(AgileDataBaseTableDto doubleWayDto)
        {
            if (doubleWayDto.Name.IsNullOrEmpty())
            {
                return OperateStatus<IEnumerable<AgileDataBaseColumnDto>>.Success(new List<AgileDataBaseColumnDto>());
            }
            var output = (await _dataBaseRepository.FindDataBaseColumns(doubleWayDto)).ToList();
            //排除
            string[] fields = { "Id", "Status", "ProcessInstanceId", "CreateUserId", "CreateUserName", "CreateOrganizationId", "CreateTime", "UpdateTime", "CreateOrganizationName", "UpdateUserId", "UpdateUserName", "UpdateOrganizationId", "UpdateOrganizationName" };
            for (int i = output.Count - 1; i >= 0; i--)
            {
                if (fields.Any(w => w.ToLower() == output[i].Name.ToLower()))
                    output.Remove(output[i]);
            }
            return OperateStatus<IEnumerable<AgileDataBaseColumnDto>>.Success(output);
        }

        /// <summary>
        /// 获取外键信息
        /// </summary>
        /// <param name="doubleWayDto"></param>
        /// <returns></returns>
        public async Task<OperateStatus<IEnumerable<AgileDataBaseFkColumnOutput>>> FindDataBasefFkColumn(AgileDataBaseTableDto doubleWayDto)
        {
            return OperateStatus<IEnumerable<AgileDataBaseFkColumnOutput>>.Success(await _dataBaseRepository.FinddatabsefFkColumn(doubleWayDto));
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<bool>> IsTableExist(AgileDataBaseIsTableExistInput input)
        {
            using (var fix = new SqlDatabaseFixture())
            {
                if (input.MenuId.HasValue)
                {
                    var config = await fix.Db.AgileConfig.SetSelect(s => new { s.ConfigId }).FindAsync(f => f.MenuId == input.MenuId && f.ConfigType == 2);
                    if (config != null)
                    {
                        input.ConfigId = config.ConfigId;
                    }
                }

                if (!input.ConfigId.IsEmptyGuid())
                {
                    var form = await fix.Db.AgileConfig.SetSelect(s => new { s.DataFromName }).FindAsync(f => f.ConfigId == input.ConfigId);
                    if (form != null)
                    {
                        input.DataFromName = form.DataFromName;
                    }
                }
            }

            return OperateStatus<bool>.Success(await _dataBaseRepository.IsTableExist(input));
        }

        /// <summary>
        /// 创建表单
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public OperateStatus SaveFormTable(AgileDataBaseSaveFormTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                switch (_connectionType)
                {
                    case ResourceDataBaseType.Mysql:
                        operateStatus = SaveFormTableMySql(input);
                        break;
                    case ResourceDataBaseType.Postgresql:
                        break;
                    default:
                        operateStatus = SaveFormTableSqlServer(input);
                        break;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            operateStatus.Code = ResultCode.Success;
            return operateStatus;
        }

        /// <summary>
        /// 创建表Mysql
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private OperateStatus SaveFormTableSqlServer(AgileDataBaseSaveFormTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                string sql;
                var isHaveTableName = IsTableExist(_connectionString, input.DataFromName);
                if (isHaveTableName.Data)
                {
                    using (var fix = new SqlDatabaseFixture())
                    {
                        var config = fix.Db.AgileConfig.SetSelect(s => new { s.DataFromName }).Find(f => f.ConfigId == input.ConfigId);
                        sql = $"EXEC sp_rename '{config.DataFromName.FilterSql()}', '{input.DataFromName.FilterSql()}'";
                    }
                    var renameTableStrResult = new DbHelper(_connectionString).ExecuteSql(sql);
                    string updateDescriptionStr = $" EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'{input.Remark}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{input.DataFromName}'";
                    new DbHelper(_connectionString).ExecuteSql(updateDescriptionStr);
                }
                else
                {
                    sql = $@"
                        CREATE TABLE [dbo].[@table](
                            [Id] int Identity(1,1) primary key NOT NULL,
                            [Status] [smallint] NULL,
                            [RelationId] [uniqueidentifier] NOT NULL,
                            [CreateTime] [datetime] not null default getdate(),
	                        [CreateUserName] [nvarchar](64) NULL,
                            [UpdateTime] [datetime]  null default getdate(),
	                        [UpdateUserName] [nvarchar](64) NULL,

                            [CreateUserId] [uniqueidentifier] NULL,
	                        [CreateOrganizationId] [uniqueidentifier] NULL,
	                        [CreateOrganizationName] [nvarchar](256) NULL,

                            [UpdateUserId] [uniqueidentifier] NULL,
	                        [UpdateOrganizationId] [uniqueidentifier] NULL,
	                        [UpdateOrganizationName] [nvarchar](256) NULL
                        )
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'Id'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'Status'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关联Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'RelationId'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CreateTime'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CreateUserId'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CreateUserName'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人组织机构Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CreateOrganizationId'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人组织机构名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CreateOrganizationName'

                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'UpdateTime'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'UpdateUserId'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'UpdateUserName'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人组织机构Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'UpdateOrganizationId'
                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人组织机构名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'UpdateOrganizationName'

                        EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{input.Remark}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{input.DataFromName}'";
                    sql = sql.Replace("@table", input.DataFromName.FilterSql());
                    new DbHelper(_connectionString).ExecuteSql(sql);
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            operateStatus.Code = ResultCode.Success;
            return operateStatus;
        }

        /// <summary>
        /// 创建表Mysql
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private OperateStatus SaveFormTableMySql(AgileDataBaseSaveFormTableInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            try
            {
                string sql;
                var isHaveTableName = IsTableExist(_connectionString, input.DataFromName);
                if (isHaveTableName.Data)
                {
                    using (var fix = new SqlDatabaseFixture())
                    {
                        var config = fix.Db.AgileConfig.SetSelect(s => new { s.DataFromName }).Find(f => f.ConfigId == input.ConfigId);
                        sql = $"ALTER TABLE {config.DataFromName} RENAME TO  {input.DataFromName}";
                    }
                    new DbHelper(_connectionString).ExecuteSql(sql);
                    string updateDescriptionStr = $" alter table {input.DataFromName} comment '{input.Remark}';";
                    new DbHelper(_connectionString).ExecuteSql(updateDescriptionStr);
                }
                else
                {
                    sql = $@"
                        CREATE TABLE {input.DataFromName}(
                            Id INT(50) primary key NOT NULL AUTO_INCREMENT COMMENT '主键',
                            Status smallint NULL COMMENT '状态',
	                        RelationId char(36)  NOT NULL COMMENT '关联主键',
                            CreateTime datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	                        CreateUserName varchar(64) NULL COMMENT '创建人',
                            UpdateTime datetime not null DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
	                        UpdateUserName varchar(64) NULL COMMENT '修改人',
                            CreateUserId char(36) NULL COMMENT '创建人Id',
                            CreateOrganizationId char(36) NULL COMMENT '创建人组织机构Id',
	                        CreateOrganizationName varchar(256) NULL COMMENT '创建人组织机构名称',
                            UpdateUserId char(36) NULL COMMENT '修改人Id',
	                        UpdateOrganizationId char(36) NULL COMMENT '修改人组织机构Id',
	                        UpdateOrganizationName varchar(256) NULL COMMENT '修改人组织机构名称'
                        )ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='{input.Remark}'
                      ";

                    new DbHelper(_connectionString).ExecuteSql(sql);
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
                return operateStatus;
            }
            operateStatus.Code = ResultCode.Success;
            return operateStatus;
        }

        /// <summary>
        /// 保存表字段
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> SaveFormTableField(AgileDataBaseSaveFormTableFieldInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //得到原有表字段
            try
            {
                var form = await _agileConfigLogic.FindAsync(f => f.ConfigId == input.ConfigId);
                if (form.FormCategory == EnumFormCategory.设计器.ToShort())
                {
                    var columns = (await _dataBaseRepository.FindDataBaseColumns(new AgileDataBaseTableDto
                    {
                        Name = form.DataFromName
                    })).ToList();

                    #region 排重
                    var fields = input.Columns.JsonStringToList<AgileDataBaseSaveFormTableFieldDetailInput>();
                    var fieldsRepeats = fields.GroupBy(x => x.Name).Where(x => x.Count() > 1).ToList();
                    if (fieldsRepeats.Any())
                    {
                        string groupByStr = string.Empty;
                        foreach (var fieldsRepeat in fieldsRepeats)
                        {
                            groupByStr += fieldsRepeat.Key + ",";
                        }
                        operateStatus.Msg = "字段：" + groupByStr.TrimEnd(',') + " 重复,请修改后重试";
                        return operateStatus;
                    }
                    #endregion

                    #region 获取新增字段
                    IList<AgileDataBaseSaveFormTableFieldDetailInput> insert = new List<AgileDataBaseSaveFormTableFieldDetailInput>();
                    IList<AgileDataBaseSaveFormTableFieldDetailInput> delete = new List<AgileDataBaseSaveFormTableFieldDetailInput>();

                    columns = (columns.Where(w => w.Name != "Id" &&
                                                  w.Name != "Status" &&
                                                  w.Name != "RelationId" &&
                                                  w.Name != "CreateTime" &&
                                                  w.Name != "CreateUserId" &&
                                                  w.Name != "CreateUserName" &&
                                                  w.Name != "CreateOrganizationId" &&
                                                  w.Name != "CreateOrganizationName" &&
                                                  w.Name != "UpdateTime" &&
                                                  w.Name != "UpdateUserId" &&
                                                  w.Name != "UpdateUserName" &&
                                                  w.Name != "UpdateOrganizationId" &&
                                                  w.Name != "UpdateOrganizationName")).ToList();

                    //获取删除字段
                    foreach (var column in columns)
                    {
                        //当前字段是否在传入中
                        var fi = fields.Where(w => w.Name == column.Name || w.Name + relationTxt == column.Name);
                        //不在则删除
                        if (!fi.Any())
                        {
                            delete.Add(new AgileDataBaseSaveFormTableFieldDetailInput { Name = column.Name });
                        }
                    }


                    //获取新增字段
                    foreach (var field in fields)
                    {
                        if (!_noCreateField.Contains(field.ControlType.ToLower()))
                        {
                            //不在则加入新增
                            if (columns.All(w => w.Name != field.Name))
                            {
                                insert.Add(field);
                            }
                            else
                            {
                                //判断是否在需要加关联id里面
                                if (_relationField.Contains(field.ControlType.ToLower()))
                                {
                                    //是否存在
                                    if (columns.All(w => w.Name != field.Name + relationTxt))
                                    {
                                        insert.Add(new AgileDataBaseSaveFormTableFieldDetailInput
                                        {
                                            Name = field.Name + relationTxt
                                        });
                                    }
                                }
                            }
                        }
                    }
                    #endregion

                    switch (_connectionType)
                    {
                        case ResourceDataBaseType.Mysql:
                            operateStatus = await SaveFormTableFieldMySql(_connectionString, form, fields, insert, delete);
                            break;
                        case ResourceDataBaseType.Postgresql:
                            break;
                        default:
                            operateStatus = await SaveFormTableFieldSqlServerSql(_connectionString, form, fields, insert, delete);
                            break;
                    }
                }
                else
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = "操作成功";
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 执行Mysql保存字段
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="form"></param>
        /// <param name="fields"></param>
        /// <param name="insert"></param>
        /// <returns></returns>
        private async Task<OperateStatus> SaveFormTableFieldSqlServerSql(
            string conn,
            AgileConfig form,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> fields,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> insert,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> delete)
        {
            OperateStatus operateStatus = new OperateStatus();
            //得到原有表字段
            try
            {
                #region 主表
                //删除字段
                if (delete.Any())
                {
                    string createTableStr = $"ALTER TABLE {form.DataFromName} DROP COLUMN {string.Join(",", delete.Select(s => "[" + s.Name + "]"))}";
                    new DbHelper(_connectionString).ExecuteSql(createTableStr);
                }
                //新增字段
                if (insert.Any())
                {
                    StringBuilder createTableStr = new StringBuilder();
                    StringBuilder descriptionStr = new StringBuilder();
                    foreach (var ins in insert)
                    {
                        createTableStr.Append($",[{ins.Name}] {ins.Type} {(ins.Null ? "NULL" : "NOT NULL")}");
                        if (_relationField.Contains(ins.ControlType.ToLower()))
                        {
                            createTableStr.Append($",[{ins.Name + relationTxt}]  {ins.Type} {(ins.Null ? "NULL" : "NOT NULL")} ");
                            descriptionStr.Append($" execute sp_addextendedproperty 'MS_Description','{ins.Description}',   'user', 'dbo', 'table', '{form.DataFromName}', 'column', '{ins.Name + relationTxt}'");
                        }
                        descriptionStr.Append($" execute sp_addextendedproperty 'MS_Description','{ins.Description}',   'user', 'dbo', 'table', '{form.DataFromName}', 'column', '{ins.Name}'");
                    }
                    new DbHelper(conn).ExecuteSql($"ALTER TABLE {form.DataFromName} ADD {createTableStr.ToString().TrimStart(',')}");
                    new DbHelper(conn).ExecuteSql(descriptionStr.ToString());
                }

                //处理附表
                foreach (var ins in fields)
                {
                    if (_multipleField.Contains(ins.ControlType.ToLower()))
                    {
                        //判断是否多选
                        var tableName = form.DataFromName + "_" + ins.Name;
                        var isHaveTableName = IsTableExist(conn, tableName);
                        //判断是否为多选
                        if (!ins.IsSingle)
                        {
                            if (!isHaveTableName.Data)
                            {
                                string sql = $@"CREATE TABLE [dbo].[@table](
	                                        [Id] int identity(1,1) primary key,
                                            [RelationId] [uniqueidentifier] NOT NULL,
                                            [CorrelationId] NVARCHAR(256) NOT NULL,
                                            [Value] NVARCHAR(256) NULL)
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{ins.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'RelationId'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务数据Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CorrelationId'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务数据值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'Value'";
                                sql = sql.Replace("@table", tableName);
                                new DbHelper(conn).ExecuteSql(sql);
                            }
                        }
                        //单选
                        else
                        {
                            if (isHaveTableName.Data)
                            {
                                new DbHelper(conn).ExecuteSql($"DROP TABLE {tableName}");
                            }
                        }
                    }
                }
                #endregion

                #region 子表 
                //判断是否具有子表
                var batchs = fields.Where(w => w.ControlType.ToLower().Contains("batch")).ToList();
                if (batchs.Any())
                {
                    foreach (var batch in batchs)
                    {
                        #region 排重
                        var subfields = HttpUtility.UrlDecode(batch.Value).JsonStringToList<AgileDataBaseSaveFormTableFieldDetailInput>();
                        var subfieldsRepeats = subfields.GroupBy(x => x.Name).Where(x => x.Count() > 1).ToList();
                        if (subfieldsRepeats.Any())
                        {
                            string subGroupByStr = string.Empty;
                            foreach (var fieldsRepeat in subfieldsRepeats)
                            {
                                subGroupByStr += fieldsRepeat.Key + ",";
                            }
                            operateStatus.Msg = $"子表【{batch.Description}{batch.Name}】字段：" + subGroupByStr.TrimEnd(',') + " 重复,请修改后重试";
                            return operateStatus;
                        }
                        #endregion

                        #region 创建附主表
                        //子表主表
                        if (!IsTableExist(conn, batch.Name).Data)
                        {
                            //新建表
                            string sql = $@"CREATE TABLE [dbo].[@table](
	                                        [Id] int Identity(1,1) primary key NOT NULL,
                                            [RelationId] [uniqueidentifier] NOT NULL)
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'Id'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'关联Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'RelationId'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{batch.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table'";
                            sql = sql.Replace("@table", batch.Name);
                            new DbHelper(conn).ExecuteSql(sql);
                        }
                        #endregion

                        var subcolumns = (await _dataBaseRepository.FindDataBaseColumns(new AgileDataBaseTableDto
                        {
                            Name = batch.Name
                        })).ToList();

                        IList<AgileDataBaseSaveFormTableFieldDetailInput> batchFieldDelete = new List<AgileDataBaseSaveFormTableFieldDetailInput>();
                        IList<AgileDataBaseSaveFormTableFieldDetailInput> batchFieldInsert = new List<AgileDataBaseSaveFormTableFieldDetailInput>();
                        subcolumns = (subcolumns.Where(w => w.Name != "Id" &&
                                                      w.Name != "RelationId")).ToList();

                        #region 删除字段 
                        foreach (var column in subcolumns)
                        {
                            //不在则删除
                            if (subfields.Where(w => w.Name == column.Name || w.Name + relationTxt == column.Name).Count() == 0)
                            {
                                batchFieldDelete.Add(new AgileDataBaseSaveFormTableFieldDetailInput { Name = column.Name });
                            }
                        }
                        //执行删除字段语句
                        if (batchFieldDelete.Any())
                        {
                            string createTableStr = $"ALTER TABLE {batch.Name} DROP COLUMN {string.Join(",", batchFieldDelete.Select(s => "[" + s.Name + "]"))}";
                            new DbHelper(conn).ExecuteSql(createTableStr);
                        }
                        #endregion

                        #region 新增字段
                        foreach (var field in subfields)
                        {
                            if (!_noCreateField.Contains(field.Type.ToLower()))
                            {
                                //不在则新增
                                if (subcolumns.Where(w => w.Name == field.Name).Count() == 0)
                                {
                                    batchFieldInsert.Add(field);
                                }
                                else
                                {
                                    //判断是否在需要加关联id里面
                                    if (_relationField.Contains(field.Type))
                                    {
                                        //是否存在
                                        if (subcolumns.Where(w => w.Name == field.Name + relationTxt).Count() == 0)
                                        {
                                            field.Name = field.Name + relationTxt;
                                            batchFieldInsert.Add(field);
                                        }
                                    }
                                }
                            }
                        }

                        //新增
                        if (batchFieldInsert.Any())
                        {
                            StringBuilder createTableStr = new StringBuilder();
                            StringBuilder descriptionStr = new StringBuilder();
                            foreach (var ins in batchFieldInsert)
                            {
                                if (ins.Type.IsNotNullOrEmpty())
                                {
                                    createTableStr.Append($",[{ins.Name}] {ins.Type} {(ins.Null ? "NULL" : " NOT NULL ")}");
                                    if (_relationField.Contains(ins.Type))
                                    {
                                        createTableStr.Append($",[{ins.Name + relationTxt}] {ins.Type} {(ins.Null ? "NULL" : " NOT NULL ")} ");
                                        descriptionStr.Append($" execute sp_addextendedproperty 'MS_Description','{ins.Description}',   'user', 'dbo', 'table', '{batch.Name}', 'column', '{ins.Name + relationTxt}'");
                                    }
                                    descriptionStr.Append($" execute sp_addextendedproperty 'MS_Description','{ins.Description}',   'user', 'dbo', 'table', '{batch.Name}', 'column', '{ins.Name}'");
                                }
                            }
                            if (createTableStr.ToString().IsNotNullOrEmpty())
                            {
                                new DbHelper(conn).ExecuteSql($"ALTER TABLE {batch.Name} ADD {createTableStr.ToString().TrimStart(',')}");
                                new DbHelper(conn).ExecuteSql(descriptionStr.ToString());
                            }
                        }

                        //处理子附表
                        foreach (var ins in subfields)
                        {
                            if (_multipleField.Contains(ins.Type))
                            {
                                //判断是否多选
                                var tableName = batch.Name + "_" + ins.Name;
                                var isHaveTableName = IsTableExist(conn, tableName);
                                //判断是否为多选
                                if (!ins.IsSingle)
                                {
                                    if (!isHaveTableName.Data)
                                    {
                                        string sql = $@"CREATE TABLE [dbo].[@table](
	                                        [Id] int identity(1,1) primary key,
                                            [RelationId] [uniqueidentifier] NOT NULL,
                                            [CorrelationId] NVARCHAR(256) NOT NULL,
                                            [Value] NVARCHAR(256) NULL)
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{ins.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'外键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'RelationId'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务数据Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'CorrelationId'
                                            EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'业务数据值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'@table', @level2type=N'COLUMN',@level2name=N'Value'";
                                        sql = sql.Replace("@table", tableName);
                                        new DbHelper(conn).ExecuteSql(sql);
                                    }
                                }
                                //单选
                                else
                                {
                                    //是否存在表
                                    if (isHaveTableName.Data)
                                    {
                                        //删除该表
                                        new DbHelper(conn).ExecuteSql($"DROP TABLE {tableName}");
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                }
                #endregion

                operateStatus.Code = ResultCode.Success;
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 执行Mysql保存字段
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="form"></param>
        /// <param name="fields"></param>
        /// <param name="insert"></param>
        /// <returns></returns>
        private async Task<OperateStatus> SaveFormTableFieldMySql(
            string conn,
            AgileConfig form,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> fields,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> insert,
            IList<AgileDataBaseSaveFormTableFieldDetailInput> delete)
        {
            OperateStatus operateStatus = new OperateStatus();
            //得到原有表字段
            try
            {
                #region 主表

                //删除字段
                if (delete.Any())
                {
                    string createTableStr = $"ALTER TABLE {form.DataFromName} {string.Join(",", delete.Select(s => "DROP " + s.Name + ""))}";
                    new DbHelper(_connectionString).ExecuteSql(createTableStr);
                }

                //新增字段
                if (insert.Any())
                {
                    List<string> insertSql = new List<string>();
                    foreach (var ins in insert)
                    {
                        var sql = ($"ALTER TABLE {form.DataFromName} ADD {ins.Name} {ins.Type} {(ins.Null ? "NULL" : "NOT NULL ")} COMMENT '{ins.Description}' FIRST ");
                        insertSql.Insert(0, sql);
                        if (_relationField.Contains(ins.ControlType.ToLower()))
                        {
                            sql = ($"ALTER TABLE {form.DataFromName} ADD {ins.Name + relationTxt} {ins.Type} {(ins.Null ? "NULL" : "NOT NULL")}  COMMENT '{ins.Description}' FIRST ");
                            insertSql.Insert(0, sql);
                        }
                    }
                    var createTableStr = insertSql.ExpandAndToString(";");
                    new DbHelper(conn).ExecuteSql(createTableStr);
                }

                //处理附表
                foreach (var ins in fields)
                {
                    if (_multipleField.Contains(ins.ControlType.ToLower()))
                    {
                        //判断是否多选
                        var tableName = form.DataFromName + "_" + ins.Name;
                        var isHaveTableName = IsTableExist(conn, tableName);
                        //判断是否为多选
                        if (!ins.IsSingle)
                        {
                            if (!isHaveTableName.Data)
                            {
                                string sql = $@"CREATE TABLE {tableName}(
	                                        Id int primary key auto_increment COMMENT '{ins.Description}',
                                            RelationId char (36) NOT NULL  COMMENT '外键',
                                            CorrelationId varchar(256) NOT NULL  COMMENT '业务数据Id',
                                            Value varchar(256) NULL  COMMENT '业务数据值')
                                            ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='{form.Name}'";
                                new DbHelper(conn).ExecuteSql(sql);
                            }
                        }
                        //单选
                        else
                        {
                            if (isHaveTableName.Data)
                            {
                                new DbHelper(conn).ExecuteSql($"DROP TABLE {tableName}");
                            }
                        }
                    }
                }
                #endregion

                #region 子表 
                //判断是否具有子表
                var batchs = fields.Where(w => w.ControlType.ToLower().Contains("batch")).ToList();
                if (batchs.Any())
                {
                    foreach (var batch in batchs)
                    {
                        #region 排重
                        var batchFields = HttpUtility.UrlDecode(batch.Value).JsonStringToList<AgileDataBaseSaveFormTableFieldDetailInput>();
                        var batchFieldsRepeats = batchFields.GroupBy(x => x.Name).Where(x => x.Count() > 1).ToList();
                        if (batchFieldsRepeats.Any())
                        {
                            string subGroupByStr = string.Empty;
                            foreach (var fieldsRepeat in batchFieldsRepeats)
                            {
                                subGroupByStr += fieldsRepeat.Key + ",";
                            }
                            operateStatus.Msg = $"子表【{batch.Description}{batch.Name}】字段：" + subGroupByStr.TrimEnd(',') + " 重复,请修改后重试";
                            return operateStatus;
                        }
                        #endregion

                        #region 创建附主表
                        //子表主表
                        if (!IsTableExist(conn, batch.Name).Data)
                        {
                            //新建表
                            string sql = $@"CREATE TABLE {batch.Name}(
	                                        Id int primary key auto_increment COMMENT '主键',
                                            BatchId char(36) NOT NULL COMMENT '附表主键',
                                            RelationId char(36) NOT NULL COMMENT '外键')
                                          ";
                            sql = sql.Replace("@table", batch.Name);
                            new DbHelper(conn).ExecuteSql(sql);
                        }
                        #endregion

                        var batchColumns = (await _dataBaseRepository.FindDataBaseColumns(new AgileDataBaseTableDto
                        {
                            Name = batch.Name
                        })).ToList();

                        IList<AgileDataBaseSaveFormTableFieldDetailInput> batchFieldDelete = new List<AgileDataBaseSaveFormTableFieldDetailInput>();
                        IList<AgileDataBaseSaveFormTableFieldDetailInput> batchFieldInsert = new List<AgileDataBaseSaveFormTableFieldDetailInput>();
                        batchColumns = (batchColumns.Where(w => w.Name != "Id" &&
                                                      w.Name != "BatchId" &&
                                                      w.Name != "RelationId")).ToList();

                        #region 删除字段 
                        foreach (var column in batchColumns)
                        {
                            //不在则删除
                            if (batchFields.Where(w => w.Name == column.Name || w.Name + relationTxt == column.Name).Count() == 0)
                            {
                                batchFieldDelete.Add(new AgileDataBaseSaveFormTableFieldDetailInput { Name = column.Name });
                            }
                        }
                        //执行删除字段语句
                        if (batchFieldDelete.Any())
                        {
                            string createTableStr = $"ALTER TABLE {batch.Name} {string.Join(",", batchFieldDelete.Select(s => "DROP " + s.Name + ""))}";
                            new DbHelper(conn).ExecuteSql(createTableStr);
                        }
                        #endregion

                        #region 新增字段
                        foreach (var field in batchFields)
                        {
                            if (!_noCreateField.Contains(field.Type.ToLower()))
                            {
                                //不在则新增
                                if (batchColumns.Where(w => w.Name == field.Name).Count() == 0)
                                {
                                    batchFieldInsert.Add(field);
                                }
                                else
                                {
                                    //判断是否在需要加关联id里面
                                    if (_relationField.Contains(field.Type))
                                    {
                                        //是否存在
                                        if (batchColumns.Where(w => w.Name == field.Name + relationTxt).Count() == 0)
                                        {
                                            field.Name = field.Name + relationTxt;
                                            batchFieldInsert.Add(field);
                                        }
                                    }
                                }
                            }
                        }

                        //新增
                        if (batchFieldInsert.Any())
                        {
                            List<string> insertSql = new List<string>();
                            foreach (var ins in insert)
                            {
                                var sql = ($"ALTER TABLE {batch.Name} ADD {ins.Name} {ins.Type} {(ins.Null ? "NULL" : " NOT NULL ")} COMMENT '{ins.Description}' FIRST ");
                                insertSql.Insert(0, sql);
                                if (_relationField.Contains(ins.ControlType.ToLower()))
                                {
                                    sql = ($"ALTER TABLE {batch.Name} ADD {ins.Name + relationTxt}  {ins.Type} {(ins.Null ? "NULL" : " NOT NULL ")} COMMENT '{ins.Description}' FIRST ");
                                    insertSql.Insert(0, sql);
                                }
                            }
                            if (insertSql.Any())
                            {
                                var createTableStr = insertSql.ExpandAndToString(";");
                                new DbHelper(conn).ExecuteSql(createTableStr);
                            }
                        }

                        //处理子附表
                        foreach (var ins in batchFields)
                        {
                            if (_multipleField.Contains(ins.ControlType.ToLower()))
                            {
                                //判断是否多选
                                var tableName = batch.Name + "_" + ins.Name;
                                var isHaveTableName = IsTableExist(conn, tableName);
                                //判断是否为多选
                                if (!ins.IsSingle)
                                {
                                    if (!isHaveTableName.Data)
                                    {
                                        string sql = $@"CREATE TABLE {tableName}(
	                                        Id int primary key auto_increment COMMENT '主键',
                                            BatchId char(36) NOT NULL COMMENT '附表主键',
                                            RelationId char(36) NOT NULL COMMENT '主表主键',
                                            CorrelationId varchar(256) NOT NULL COMMENT '关联数据',
                                            Value varchar(256) NULL COMMENT '值') ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='{form.Name}' ";
                                        new DbHelper(conn).ExecuteSql(sql);
                                    }
                                }
                                //单选
                                else
                                {
                                    //是否存在表
                                    if (isHaveTableName.Data)
                                    {
                                        //删除该表
                                        new DbHelper(conn).ExecuteSql($"DROP TABLE {tableName}");
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                }
                #endregion

                operateStatus.Code = ResultCode.Success;
            }
            catch (Exception e)
            {
                operateStatus.Msg = e.Message;
            }
            return operateStatus;
        }

        #region 判断数据库中，指定表是否存在
        /// <summary>
        /// 判断数据库表是否存在
        /// </summary>
        /// <param name="conn">数据库连接字符串</param>
        /// <param name="tb">数据表名</param>
        /// <returns>true:表示数据表已经存在；false，表示数据表不存在</returns>
        public OperateStatus<bool> IsTableExist(string conn, string tb)
        {
            string createDbStr = string.Empty;
            switch (_connectionType)
            {
                case ResourceDataBaseType.Mysql:
                    createDbStr = $"select 1 from information_schema.TABLES t where t.TABLE_NAME ='{tb}'";
                    break;
                case ResourceDataBaseType.Postgresql:
                    break;
                default:
                    createDbStr = $"select 1 from  sysobjects where  id = object_id('{tb}') and type = 'U'";
                    break;
            }
            //在指定的数据库中  查找 该表是否存在
            DataTable dt = new DbHelper(conn).CreateSqlDataTable(createDbStr);
            return OperateStatus<bool>.Success(dt.Rows.Count != 0);
        }

        #endregion

        /// <summary>
        /// 根据主键获取信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<object>> FindBusinessDataById(AgileDataBaseFindBusinessDataByIdInput input)
        {
            using (var fix = new SqlDatabaseFixture())
            {
                var codeGeneration = await fix.Db.AgileConfig.SetSelect(s => new { s.DataFromName }).FindAsync(f => f.ConfigId == input.ConfigId);
                string sql = $"SELECT * FROM {codeGeneration.DataFromName} WHERE RelationId='{input.Id}'";
                return OperateStatus<object>.Success(await fix.Db.Connection.QueryFirstOrDefaultAsync(sql));
            }
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> SaveBusinessData(AgileDataBaseSaveBusinessDataInput input)
        {
            OperateStatus<Guid> operateStatus = new OperateStatus<Guid>();
            //主键Id
            Guid id = CombUtil.NewComb();
            try
            {
                //若是从流程中来则将流程实例Id赋予
                if (input.ProcessInstanceId.HasValue)
                {
                    input.RelationId = (Guid)input.ProcessInstanceId;
                }
                if (input.ControlsString.IsNotNullOrEmpty())
                {
                    input.Columns = input.ControlsString.JsonStringToList<AgileDataBaseSaveBusinessDataColumns>();
                }
                var form = await _agileConfigLogic.FindAsync(f => f.ConfigId == input.ConfigId);
                //主表所有字段
                var allColumns = (await _dataBaseRepository.FindDataBaseColumns(new AgileDataBaseTableDto
                {
                    Name = form.DataFromName
                })).ToList();
                IList<AgileDataBaseSaveBusinessDataColumns> columns = new List<AgileDataBaseSaveBusinessDataColumns>();
                //插入
                if (input.Columns != null)
                {
                    foreach (var column in input.Columns)
                    {
                        if (column.Name.IsNotNullOrEmpty())
                        {
                            //字表
                            if (column.Type.ToUpper() == "BATCH")
                            {
                                columns.Add(column);
                            }
                            else
                            {
                                var colums = allColumns.ToList().Any(w => w.Name.ToUpper() == column.Name.ToUpper());
                                if (colums)
                                    columns.Add(column);
                            }
                        }
                    }
                }
                string sql;

                IList<Guid> detailIdList = new List<Guid>();
                IList<DataBaseBatchIdsOutput> batchIds = new List<DataBaseBatchIdsOutput>();
                var selectSql = $"SELECT * FROM {form.DataFromName} WHERE RelationId='{input.RelationId}'";
                DataTable createDataTable = new DbHelper(_connectionString).CreateSqlDataTable(selectSql);
                //表数据
                bool haveData = createDataTable.Rows.Count > 0;
                IList<string> doSqls = new List<string>();
                //新增
                if (!haveData)
                {
                    id = input.RelationId;
                    StringBuilder stringBuilder = new StringBuilder();
                    StringBuilder stringBuilderValues = new StringBuilder();
                    stringBuilder.Append($"INSERT INTO {form.DataFromName} (");
                    if (input.Columns != null)
                    {
                        #region 主表

                        //主表
                        var insertControls = columns.Where(item => item.Name.ToUpper() != "RELATIONID"
                                                                                                 && !item.Value.IsNullOrEmpty()
                                                                                                 && !_noCreateField.Contains(item.Type.ToLower()));
                        foreach (var item in insertControls)
                        {
                            stringBuilder.Append($"{item.Name},");
                            item.Value = _filterSqlField.Any(f => f == item.Type) ? item.Value : item.Value.FilterSql().Xss();
                            stringBuilderValues.Append(item.Type == "switch" ? $"{item.Value}," : $"N'{item.Value}',");
                        }

                        //主表附表
                        var parTable = columns.Where(item => item.Name.ToUpper() != "RELATIONID"
                                                             && !item.Value.IsNullOrEmpty()
                                                             && !_noCreateField.Contains(item.Type.ToLower())
                                                             && _multipleField.Contains(item.Type.ToLower())
                                                             && !item.IsSingle).ToList();
                        foreach (var item in parTable)
                        {
                            var tableName = form.DataFromName + "_" + item.Name;
                            if (IsTableExist(_connectionString, tableName).Data)
                            {
                                //得到Value和Id集合
                                var ids = item.Value.Split(",");
                                var values = columns
                                    .FirstOrDefault(f => f.Name == item.Name + relationTxt)
                                    ?.Value.Split(",");
                                for (int i = 0; i < values.Length; i++)
                                {
                                    string collSql =
                                        ($@"INSERT INTO {tableName}
                                                        (RelationId
                                                        ,CorrelationId
                                                        ,Value)
                                                        VALUES
                                                        ('{id}'
                                                        ,'{ids[i]}'
                                                        ,'{values[i]}')");
                                    doSqls.Add(collSql);
                                }
                            }
                        }


                        #endregion

                        #region 子表

                        var batchControls = columns.Where(w => w.Type.ToUpper() == "BATCH");
                        foreach (var item in batchControls)
                        {
                            if (item.Value.IsNotNullOrEmpty())
                            {
                                //序列化得到子表参数
                                var subtable = HttpUtility.UrlDecode(item.Value).JsonStringToObject<DataBaseBatchControlOutput>();
                                foreach (var value in subtable.Values)
                                {
                                    var subtableBuilder = new StringBuilder();
                                    var subtableBuilderValues = new StringBuilder();
                                    var detailId = CombUtil.NewComb();
                                    subtableBuilder.Append($"INSERT INTO {subtable.Table} (");
                                    subtableBuilder.Append("RelationId,");//外键
                                    subtableBuilderValues.Append($"'{id}',");
                                    foreach (var detail in value.Detail)
                                    {
                                        if (detail.Name.IsNotNullOrEmpty() && detail.Value.IsNotNullOrEmpty() && detail.Name != "BatchId")
                                        {
                                            subtableBuilder.Append("" + detail.Name + ",");
                                            subtableBuilderValues.Append(detail.Type == "switch" ? $"{detail.Value}," : $"N'{detail.Value}',");
                                        }
                                        if (detail.Name == "BatchId")
                                        {
                                            subtableBuilder.Append("BatchId,");
                                            subtableBuilderValues.Append(detail.Value.IsNotNullOrEmpty() ? $"N'{detail.Value}'," : $"N'{detailId}',");
                                            detailIdList.Add(detail.Value.IsNotNullOrEmpty() ? Guid.Parse(detail.Value) : detailId);
                                        }
                                    }
                                    string batchsql = subtableBuilder.ToString().TrimEnd(',') + " ) VALUES (" + subtableBuilderValues.ToString().TrimEnd(',') + ")";
                                    if (batchsql.IsNotNullOrEmpty())
                                    {
                                        doSqls.Add(batchsql);
                                    }
                                }
                            }
                        }


                        var batchParTable = (columns.Where(item => item.Type.ToUpper() == "BATCH" && !item.Value.IsNullOrEmpty())).ToList();
                        foreach (var item in batchParTable)
                        {
                            //序列化得到子表参数
                            var subtable = HttpUtility.UrlDecode(item.Value).JsonStringToObject<DataBaseBatchControlOutput>();
                            for (int i = 0; i < subtable.Values.Count; i++)
                            {
                                var keyvalue = subtable.Values[i];
                                foreach (var detail in keyvalue.Detail)
                                {
                                    if (!detail.Value.IsNullOrEmpty()
                                        && !_noCreateField.Contains(detail.Type.ToLower())
                                        && _multipleField.Contains(detail.Type.ToLower())
                                        && !detail.IsSingle)
                                    {
                                        var tableName = subtable.Table + "_" + detail.Name;
                                        if (IsTableExist(_connectionString, tableName).Data)
                                        {
                                            //得到Value和Id集合
                                            var ids = detail.Value.Split(",");
                                            var values = keyvalue.Detail.FirstOrDefault(f => f.Name == detail.Name + relationTxt)?.Value.Split(",");
                                            for (int j = 0; j < values.Length; j++)
                                            {
                                                string batchsql = ($@"INSERT INTO {tableName}
                                                                                    (BatchId
                                                                                    ,RelationId
                                                                                    ,CorrelationId
                                                                                    ,Value)
                                                                                    VALUES
                                                                                    ('{detailIdList[i]}'
                                                                                    ,'{id}'
                                                                                    ,'{ids[j]}'
                                                                                    ,'{values[j]}')");
                                                doSqls.Add(batchsql);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        #endregion
                    }

                    //主键
                    stringBuilder.Append("RelationId,");
                    stringBuilderValues.Append($"'{id}',");

                    stringBuilder.Append("CreateUserId,");
                    stringBuilderValues.Append($"'{input.UserId}',");

                    stringBuilder.Append("CreateUserName,");
                    stringBuilderValues.Append($"'{input.UserName}',");

                    stringBuilder.Append("CreateOrganizationId,");
                    stringBuilderValues.Append($"'{input.OrganizationId}',");

                    stringBuilder.Append("CreateOrganizationName,");
                    stringBuilderValues.Append($"'{input.OrganizationName}',");

                    stringBuilder.Append("UpdateUserId,");
                    stringBuilderValues.Append($"'{input.UserId}',");

                    stringBuilder.Append("UpdateUserName,");
                    stringBuilderValues.Append($"'{input.UserName}',");

                    stringBuilder.Append("UpdateOrganizationId,");
                    stringBuilderValues.Append($"'{input.OrganizationId}',");

                    stringBuilder.Append("UpdateOrganizationName,");
                    stringBuilderValues.Append($"'{input.OrganizationName}',");

                    //拼接Sql
                    sql = stringBuilder.ToString().TrimEnd(',') + " ) VALUES (" +
                          stringBuilderValues.ToString().TrimEnd(',') + ")";

                    doSqls.Add(sql);
                }
                //修改
                else
                {
                    StringBuilder stringBuilder = new StringBuilder();
                    stringBuilder.Append($"UPDATE {form.DataFromName} SET ");
                    if (input.Columns != null)
                    {
                        id = input.RelationId;
                        #region 主表
                        var updateControls = columns.Where(item => item.Name.ToUpper() != "RELATIONID"
                                                                    && item.Name.ToUpper() != "STATUS"
                                                                    && item.Type.ToUpper() != "IMAGESHOW"
                                                                    && !_noCreateField.Contains(item.Type.ToLower()));
                        foreach (var item in updateControls)
                        {
                            item.Value = _filterSqlField.Any(f => f == item.Type) ? item.Value : item.Value.FilterSql().Xss();
                            stringBuilder.Append(!item.Value.IsNullOrEmpty()
                                ? $"{item.Name}={(item.Type == "switch" ? $"{item.Value}," : $"N'{item.Value}', ")}"
                                : $"{item.Name}=null,");
                        }

                        //主表附表
                        var parTable = columns.Where(item =>
                              item.IsDelete
                           && item.Name.ToUpper() != "RELATIONID"
                           && !item.Value.IsNullOrEmpty()
                           && item.Name.ToUpper() != "STATUS"
                           && !_noCreateField.Contains(item.Type.ToLower())
                           && _multipleField.Contains(item.Type.ToLower())).ToList();
                        foreach (var item in parTable)
                        {
                            var tableName = form.DataFromName + "_" + item.Name;
                            if (IsTableExist(_connectionString, tableName).Data)
                            {
                                //删除
                                string delete = $"DELETE FROM {tableName} WHERE RelationId ='{id}'";
                                doSqls.Add(delete);
                                //删除附表值,再进行新增
                                if (!item.IsSingle)
                                {
                                    //得到Value和Id集合
                                    var ids = item.Value.Split(",");
                                    var values = columns.FirstOrDefault(f => f.Name == item.Name + relationTxt)?.Value.Split(",");
                                    for (int i = 0; i < values.Length; i++)
                                    {
                                        string collSql = ($@"INSERT INTO {tableName}
                                                        (RelationId
                                                        ,CorrelationId
                                                        ,Value )
                                                        VALUES
                                                        ('{id}'
                                                        ,'{ids[i]}'
                                                        ,'{values[i]}')");
                                        doSqls.Add(collSql);
                                    }
                                }
                            }
                        }
                        #endregion

                        #region 子表

                        var updateBatchControls = columns.Where(item => item.IsDelete && item.Type.ToUpper() == "BATCH" && !item.Value.IsNullOrEmpty());

                        foreach (var item in updateBatchControls)
                        {
                            //需要新增控件
                            var insertDataBaseBatchControl = new List<DataBaseBatchControlDetailOutput>();
                            //需要删除数据
                            var deleteIds = new List<Guid>();

                            //序列化得到子表参数
                            var subtable = HttpUtility.UrlDecode(item.Value).JsonStringToObject<DataBaseBatchControlOutput>();

                            //查询
                            string subselect = $"SELECT BatchId FROM {subtable.Table} WHERE RelationId ='{id}' ";
                            var subdt = new DbHelper(_connectionString).CreateSqlDataTable(subselect);
                            if (subdt.Rows.Count > 0)
                            {
                                foreach (DataRow row in subdt.Rows)
                                {
                                    batchIds.Add(new DataBaseBatchIdsOutput
                                    {
                                        BatchId = (Guid)row["BatchId"],
                                        RelationId = id,
                                    });
                                }

                                foreach (DataRow row in subdt.Rows)
                                {
                                    DataBaseControlsOutput dataBaseControlsOutput = null;
                                    //得到所有传入数据
                                    var batchId = (Guid)row["BatchId"];
                                    foreach (var keyvalue in subtable.Values)
                                    {
                                        dataBaseControlsOutput = keyvalue.Detail.FirstOrDefault(f => f.Name == "BatchId" && f.Value == batchId.ToString());
                                        if (dataBaseControlsOutput != null)
                                        {
                                            break;
                                        }
                                    }
                                    if (dataBaseControlsOutput == null)
                                    {
                                        deleteIds.Add(batchId);
                                    }
                                }
                            }

                            //得到所有传入数据
                            foreach (var keyvalue in subtable.Values)
                            {
                                var idControl = keyvalue.Detail.FirstOrDefault(f => f.Name == "BatchId");
                                var subtableId = batchIds.FirstOrDefault(f => f.BatchId == Guid.Parse(idControl.Value));
                                if (subtableId == null)
                                {
                                    insertDataBaseBatchControl.Add(keyvalue);
                                }
                                else
                                {
                                    insertDataBaseBatchControl.Add(keyvalue);
                                    deleteIds.Add(Guid.Parse(idControl.Value));
                                }
                            }
                            //删除数据
                            foreach (var deleteId in deleteIds)
                            {
                                doSqls.Add($"DELETE FROM {subtable.Table} WHERE BatchId ='{deleteId}'");
                            }

                            //新增数据
                            foreach (var keyvalue in insertDataBaseBatchControl)
                            {
                                var subtableBuilder = new StringBuilder();
                                var subtableBuilderValues = new StringBuilder();
                                var detailId = CombUtil.NewComb();
                                subtableBuilder.Append($"INSERT INTO {subtable.Table} (");
                                subtableBuilder.Append("RelationId,");
                                subtableBuilderValues.Append($"'{id}',");
                                foreach (var detail in keyvalue.Detail)
                                {
                                    if (detail.Name.IsNotNullOrEmpty() && detail.Value.IsNotNullOrEmpty() && detail.Name != "BatchId")
                                    {
                                        item.Value = _filterSqlField.Any(f => f == item.Type) ? item.Value : item.Value.FilterSql().Xss();
                                        subtableBuilder.Append("" + detail.Name + ",");
                                        subtableBuilderValues.Append(detail.Type == "switch" ? $"{detail.Value}," : $"N'{detail.Value}',");
                                    }
                                    if (detail.Name == "BatchId")
                                    {
                                        subtableBuilder.Append("BatchId,");
                                        subtableBuilderValues.Append(detail.Value.IsNotNullOrEmpty() ? $"N'{detail.Value}'," : $"N'{detailId}',");
                                        detailIdList.Add(detail.Value.IsNotNullOrEmpty() ? Guid.Parse(detail.Value) : detailId);
                                    }
                                }

                                string batchsql =
                                    subtableBuilder.ToString().TrimEnd(',') + " ) VALUES (" +
                                    subtableBuilderValues.ToString().TrimEnd(',') + ")";
                                if (batchsql.IsNotNullOrEmpty())
                                {
                                    doSqls.Add(batchsql);
                                }
                            }
                        }

                        //子表附表
                        var subParTable = columns.Where(item => item.IsDelete
                            && item.Type.ToUpper() == "BATCH" && !item.Value.IsNullOrEmpty()).ToList();
                        foreach (var item in subParTable)
                        {
                            //序列化得到子表参数
                            var subtable = HttpUtility.UrlDecode(item.Value).JsonStringToObject<DataBaseBatchControlOutput>();
                            for (int i = 0; i < subtable.Values.Count; i++)
                            {
                                var keyvalue = subtable.Values[i];
                                for (int j = 0; j < keyvalue.Detail.Count; j++)
                                {
                                    var detail = keyvalue.Detail[j];
                                    if (!detail.Value.IsNullOrEmpty()
                                        && !_noCreateField.Contains(detail.Type.ToLower())
                                        && _multipleField.Contains(detail.Type.ToLower())
                                        && !detail.IsSingle)
                                    {
                                        var tableName = subtable.Table + "_" + detail.Name;
                                        if (IsTableExist(_connectionString, tableName).Data)
                                        {
                                            //删除
                                            string delete = $"DELETE FROM {tableName} WHERE RelationId='{batchIds[i].RelationId}' and BatchId='{batchIds[i].BatchId}'";
                                            doSqls.Add(delete);

                                            //得到Value和Id集合
                                            var values = detail.Value.Split(",");
                                            var ids = keyvalue.Detail.FirstOrDefault(f => f.Name == detail.Name + relationTxt)?.Value.Split(",");
                                            for (int k = 0; k < values.Length; k++)
                                            {
                                                string batchsql = ($@"INSERT INTO {tableName}
                                                                                    (BatchId
                                                                                    ,RelationId
                                                                                    ,CorrelationId
                                                                                    ,Value)
                                                                                    VALUES
                                                                                    ('{detailIdList[i]}'
                                                                                    ,'{id}'
                                                                                    ,'{ids[k]}'
                                                                                    ,'{values[k]}')");
                                                doSqls.Add(batchsql);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        #endregion
                    }


                    stringBuilder.Append($"UpdateTime='{DateTime.Now}',");
                    stringBuilder.Append($"UpdateUserId='{input.UserId}',");
                    stringBuilder.Append($"UpdateUserName='{input.UserName}',");
                    stringBuilder.Append($"UpdateOrganizationId='{input.OrganizationId}',");
                    stringBuilder.Append($"UpdateOrganizationName='{input.OrganizationName}',");


                    sql = stringBuilder.ToString().TrimEnd(',') + $" WHERE RelationId='{input.RelationId}' ";
                    doSqls.Add(sql);
                }

                if (doSqls.Any())
                {
                    using (IDbConnection connection = GetConnectoin(_connectionString))
                    {
                        var trans = connection.BeginTransaction();
                        try
                        {
                            //循环执行
                            foreach (var item in doSqls)
                            {
                                connection.Execute(item, null, trans);
                            }
                            trans.Commit();
                            operateStatus.Code = ResultCode.Success;
                            operateStatus.Msg = Chs.Successful;
                            operateStatus.Data = id;
                        }
                        catch (Exception ex)
                        {
                            operateStatus.Msg = "操作失败";
                            operateStatus.Code = ResultCode.Error;
                            trans.Rollback();
                            throw ex;
                        }
                    }
                }
                //未找到表单默认为成功
                else
                {
                    operateStatus.Code = ResultCode.Success;
                }
            }
            catch (Exception e)
            {
                operateStatus.Msg = "操作失败";
                operateStatus.Code = ResultCode.Error;
                throw e;
            }
            return operateStatus;
        }

        /// <summary>
        /// 数据库链接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private IDbConnection GetConnectoin(string connectionString)
        {
            var connectionType = ConfigurationUtil.GetSection("EIP:ConnectionType").ToLower();
            DbConnection connection;
            switch (connectionType)
            {
                case ResourceDataBaseType.Mysql:
                    connection = new MySqlConnection(connectionString);
                    break;
                case ResourceDataBaseType.Postgresql:
                    connection = new NpgsqlConnection(connectionString);
                    break;
                default:
                    connection = new SqlConnection(connectionString);
                    break;
            }
            if (connection.State != ConnectionState.Open)
                connection.Open();
            return connection;
        }

        /// <summary>
        /// 查询业务数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindBusinessData(AgileDataBaseFindPagingBusinessDataInput input)
        {
            //获取模板数据
            var operateStatus = new OperateStatus<dynamic>();
            try
            {
                using (var fix = new SqlDatabaseFixture())
                {
                    var codeGeneration = await fix.Db.AgileConfig.SetSelect(s => new { s.DataFromName, s.PublicJson }).FindAsync(f => f.ConfigId == input.ConfigId);
                    if (codeGeneration.DataFrom == EnumDataFrom.存储过程.ToShort())
                    {
                        var parms = new DynamicParameters();
                        string sql = input.Sql +
                                        (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                        (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                        if (input.IsPaging)
                        {
                            StringBuilder sidx = new StringBuilder();
                            StringBuilder orderBy = new StringBuilder();
                            if (input.Sidx.IsNotNullOrEmpty())
                            {
                                for (int i = 0; i < input.Sidx.Split(',').Length; i++)
                                {
                                    sidx.Append($"{codeGeneration.DataFromName + "." + input.Sidx.Split(',')[i]},");
                                    orderBy.Append($"{codeGeneration.DataFromName + "." + input.Sidx.Split(',')[i]} {input.Sord.Split(',')[i]},");
                                }
                            }
                            parms.Add("Sidx", sidx.ToString().TrimEnd(','));//排序字段
                            parms.Add("Field", input.Fields);
                            parms.Add("Filters", sql);
                            parms.Add("PageIndex", input.Current);
                            parms.Add("PageSize", input.Size);
                            parms.Add("GroupBy", input.Group.IsNullOrEmpty() ? "" : input.Group);
                            parms.Add("Sort", orderBy.ToString().TrimEnd(','));
                            parms.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
                            var pagerInfo = new PagerInfo();
                            using (var db = GetConnectoin(_connectionString))
                            {
                                var data = (await db.QueryAsync<dynamic>(codeGeneration.DataFromName, parms, commandType: CommandType.StoredProcedure)).ToList();
                                if (data.Any())
                                {
                                    pagerInfo.RecordCount = parms.Get<int>("RecordCount");
                                }
                                pagerInfo.Current = input.Current;
                                pagerInfo.PageCount = (pagerInfo.RecordCount + input.Size - 1) / input.Size; //页总数 
                                dynamic v = new ExpandoObject();
                                v.code = 0;
                                v.msg = "";
                                v.page = input.Current;
                                v.count = pagerInfo.RecordCount;
                                v.data = data;
                                return OperateStatus<dynamic>.Success(v);

                            }
                        }
                        else
                        {
                            using (var db = GetConnectoin(_connectionString))
                            {
                                var data = (await db.QueryAsync<dynamic>(codeGeneration.DataFromName, parms, commandType: CommandType.StoredProcedure)).ToList();
                                dynamic v = new ExpandoObject();
                                v.code = 0;
                                v.msg = "";
                                v.page = input.Current;
                                v.count = data.Count;
                                v.data = data;
                                return OperateStatus<dynamic>.Success(v);
                            }
                        }
                    }
                    else
                    {
                        //得到需要查询的字段
                        var tables = codeGeneration.PublicJson.JsonStringToObject<AgileDataBaseFindPagingBusinessDataTableInput>();
                        //判断是否分页

                        StringBuilder fields = new StringBuilder();
                        //得到需要查询的字段
                        foreach (var item in tables.Columns.Where(w => w.IsSearch == "true"))
                        {
                            //TODO:进行多数据库处理
                            if (item.Type == "image")
                            {
                                fields.Append($"(select top 1 Path from System_File where REPLACE(CorrelationId,'|{item.Name}','')=convert(nvarchar(1024),Id) order by CreateTime desc) {item.Name},");
                            }
                            else
                            {
                                fields.Append(codeGeneration.DataFromName + "." + item.Name + ",");
                            }
                        }
                        input.Fields = fields.ToString().IsNullOrEmpty() ? "*" : fields.ToString().TrimEnd(',');
                        //input.Sidx = (input.Sidx.IsNullOrEmpty() ? "CreateTime" : input.Sidx);

                        string sql = input.Sql +
                                      (input.Where.IsNotNullOrEmpty() ? " and " + input.Where : "") +
                                      (input.DataSql.IsNotNullOrEmpty() ? " and " + input.DataSql : "");
                        StringBuilder orderBy = new StringBuilder();
                        StringBuilder sidx = new StringBuilder();
                        if (input.Sidx.IsNotNullOrEmpty())
                        {
                            for (int i = 0; i < input.Sidx.Split(',').Length; i++)
                            {
                                sidx.Append($"{codeGeneration.DataFromName + "." + input.Sidx.Split(',')[i]},");
                                orderBy.Append($"{codeGeneration.DataFromName + "." + input.Sidx.Split(',')[i]} {input.Sord.Split(',')[i]},");
                            }
                        }
                        if (input.IsPaging)
                        {
                            var parms = new DynamicParameters();
                            parms.Add("TableName", codeGeneration.DataFromName);
                            parms.Add("Sidx", sidx.ToString().TrimEnd(','));//排序字段
                            parms.Add("Field", input.Fields);
                            parms.Add("Filters", sql);
                            parms.Add("PageIndex", input.Current);
                            parms.Add("PageSize", input.Size);
                            parms.Add("GroupBy", input.Group.IsNullOrEmpty() ? "" : input.Group);
                            parms.Add("Sort", orderBy.ToString().TrimEnd(','));
                            parms.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
                            var pagerInfo = new PagerInfo();
                            var data = (await fix.Db.Connection.QueryAsync<dynamic>("System_Proc_Paging", parms, commandType: CommandType.StoredProcedure)).ToList();
                            if (data.Any())
                            {
                                pagerInfo.RecordCount = parms.Get<int>("RecordCount");
                            }
                            pagerInfo.Current = input.Current;
                            pagerInfo.PageCount = (pagerInfo.RecordCount + input.Size - 1) / input.Size; //页总数 
                            dynamic v = new ExpandoObject();
                            v.code = 0;
                            v.msg = "";
                            v.page = input.Current;
                            v.count = pagerInfo.RecordCount;
                            v.data = data;
                            return OperateStatus<dynamic>.Success(v);
                        }
                        else
                        {
                            var orderBySql = orderBy.ToString().TrimEnd(',');
                            if (orderBySql.Length > 0)
                            {
                                orderBySql = "ORDER BY " + orderBySql;
                            }
                            string selectSql = $"SELECT {input.Fields} FROM {codeGeneration.DataFromName} where 1=1 {sql} {orderBySql}";
                            var data = (await fix.Db.Connection.QueryAsync<dynamic>(selectSql)).ToList();
                            dynamic v = new ExpandoObject();
                            v.code = 0;
                            v.msg = "";
                            v.count = data.ToList().Count;
                            v.data = data;
                            return OperateStatus<dynamic>.Success(v);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                operateStatus.Msg = ex.Message;
            }
            return operateStatus;
        }

        /// <summary>
        /// 获取子表数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus<dynamic>> FindFromSubTable(DataBaseSubTableDto input)
        {
            using (var db = GetConnectoin(_connectionString))
            {
                string sql = $"SELECT * FROM {input.Table} WHERE RelationId='{input.Id}' ";
                return OperateStatus<object>.Success(await db.QueryAsync(sql));
            }
        }


        /// <summary>
        /// 根据Sql查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public OperateStatus<dynamic> FindFormSourceData(AgileDataBaseFindFormSourceDataInput input)
        {
            try
            {
                using (var db = GetConnectoin(_connectionString))
                {
                    var fields = input.Fields.IsNotNullOrEmpty() ? input.Fields.Split(',').Distinct().ExpandAndToString() : "";
                    if (input.Key.IsNotNullOrEmpty())
                    {
                        fields += fields.IsNotNullOrEmpty() ? "," + input.Key : input.Key;
                    }
                    if (input.Value.IsNotNullOrEmpty())
                    {
                        fields += fields.IsNotNullOrEmpty() ? "," + input.Value : input.Value;
                    }
                    var sql = $"SELECT {fields} FROM {input.Table} WHERE 1=1 ";
                    if (input.Sql.IsNotNullOrEmpty())
                    {
                        sql += input.Sql;
                    }
                    if (input.Sidx.IsNotNullOrEmpty())
                    {
                        sql += " order by " + input.Sidx + " " + input.Sord;
                    }
                    return OperateStatus<dynamic>.Success(db.Query<object>(sql));
                }
            }
            catch (Exception)
            {
                return OperateStatus<dynamic>.Error("查询失败");
            }
        }

        /// <summary>
        /// 根据Sql查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public OperateStatus<dynamic> FindFormSourceDataPaging(AgileDataBaseFindFormSourceDataInput input)
        {
            try
            {
                using (var db = GetConnectoin(_connectionString))
                {
                    var fields = input.Fields.IsNotNullOrEmpty() ? input.Fields.Split(',').Distinct().ExpandAndToString() : "";
                    if (_connectionType == ResourceDataBaseType.Mysql)
                    {
                        var currentPage = input.Current; //当前页号
                        var pageSize = input.Size; //每页记录数
                        var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                        var upper = currentPage * pageSize; //记录终点
                        var querySql = $"SELECT {fields},@rowNumber, @recordCount  FROM {input.Table} @where ";
                        DynamicParameters parameters = new DynamicParameters();
                        var sql = $@"select * from ({querySql} order by {input.Sidx} ) seq  limit {lower - 1},{upper}";
                        var recordCount = sql.Split("@recordCount");
                        var selectSql = recordCount[0].Trim().TrimEnd(',');
                        sql = selectSql + " " + recordCount[1];
                        sql = sql.Replace("@rowNumber", " 1 ")
                            .Replace("@orderBy", " 2 ")
                            .Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(input.Sql) ? string.Empty : input.Sql));

                        var querySqlRecordCount = querySql.Split("@recordCount");
                        var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 " + (string.IsNullOrWhiteSpace(input.Sql) ? string.Empty : input.Sql));
                        //总数量
                        sql += recordCountSql;
                        var queryMulti = db.QueryMultiple(sql, parameters);
                        var data = queryMulti.Read<dynamic>().ToList();
                        dynamic v = new ExpandoObject();
                        v.page = input.Current;
                        v.count = queryMulti.Read<long>().Sum();
                        v.data = data;
                        return OperateStatus<dynamic>.Success(v);
                    }
                    else
                    {
                        var querySql = $"SELECT {fields},@rowNumber, @recordCount  FROM {input.Table} @where ";
                        if (input.Sql.IsNotNullOrEmpty())
                        {
                            querySql += input.Sql;
                        }
                        var sql = input.IsReport ?
                            $@"select * from ({querySql}) seq "
                            : $@"select * from ({querySql}) seq where seq.rownum between @x and @y";
                        var currentPage = input.Current; //当前页号
                        var pageSize = input.Size; //每页记录数
                        var lower = ((currentPage - 1) * pageSize) + 1; //记录起点
                        var upper = currentPage * pageSize; //记录终点
                        DynamicParameters parameters = new DynamicParameters();
                        parameters.Add("x", lower);
                        parameters.Add("y", upper);
                        //排序字段
                        var orderString = $"{input.Sidx}";
                        sql = sql.Replace("@recordCount", " count(*) over() as RecordCount ")
                            .Replace("@rowNumber", " row_number() over (order by @orderBy) as rownum ")
                            .Replace("@orderBy", orderString)
                             .Replace("@where", " WHERE 1=1 ");

                        var querySqlRecordCount = querySql.Split("@recordCount");
                        var recordCountSql = querySql.Replace(querySqlRecordCount[0], ";select count(*) ").Replace("@recordCount", "  ").Replace("@where", " WHERE 1=1 ");
                        //总数量
                        sql += recordCountSql;
                        var queryMulti = db.QueryMultiple(sql, parameters);
                        var data = queryMulti.Read<dynamic>().ToList();
                        dynamic v = new ExpandoObject();
                        v.page = input.Current;
                        v.count = queryMulti.Read<long>().Sum();
                        v.data = data;
                        return OperateStatus<dynamic>.Success(v);
                    }
                }
            }
            catch (Exception ex)
            {
                return OperateStatus<dynamic>.Error(ex.Message);
            }

        }
        /// <summary>
        /// 需要处理子表情况
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<OperateStatus> DeleteBusinessData(AgileDataBaseDeleteBusinessDataInput input)
        {
            OperateStatus operateStatus = new OperateStatus();
            //获取模板数据
            using (var fix = new SqlDatabaseFixture())
            {
                var agileConfig = await fix.Db.AgileConfig.SetSelect(s => new { s.PublicJson, s.DataFromName }).FindAsync(f => f.ConfigId == input.ConfigId);
                //判断是否分页
                StringBuilder stringBuilder = new StringBuilder();
                //获取信息
                if (agileConfig.PublicJson.IsNotNullOrEmpty())
                {
                    var names = agileConfig.PublicJson.JsonStringToObject<AgileDataBaseFindPagingBusinessDataTableInput>().Columns.Where(w => w.Type.IsNotNullOrEmpty() && w.Type.ToUpper() == "BATCH");
                    if (names.Any())
                    {
                        foreach (var item in names)
                        {
                            foreach (var id in input.Id.Split(","))
                            {
                                stringBuilder.Append($" DELETE FROM {item.Name} WHERE ForeignId='{id}' ;");
                                stringBuilder.Append($" DELETE FROM System_File WHERE CorrelationId like'%{id}|%' ;");
                            }
                        }
                    }
                }
                stringBuilder.Append($" DELETE FROM {agileConfig.DataFromName} WHERE RelationId in ({input.Id.InSql()}) ");
                if (await fix.Db.Connection.ExecuteAsync(stringBuilder.ToString()) > 0)
                {
                    operateStatus.Code = ResultCode.Success;
                    operateStatus.Msg = Chs.Successful;
                }
            }

            return operateStatus;
        }
        #endregion


    }
}